package com.pucong.gitee.database.backup.business.database.service;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.pucong.gitee.database.backup.business.database.mapper.AutoDataSourceMapper;
import com.pucong.gitee.database.backup.business.database.mapper.AutoExeSqlLogMapper;
import com.pucong.gitee.database.backup.business.database.util.BackupServiceFactory;
import com.pucong.gitee.database.backup.business.database.util.OperationService;
import com.pucong.gitee.database.backup.business.sys.mapper.SysFileMapper;
import com.pucong.gitee.database.backup.business.sys.mapper.SysUserInfoMapper;
import com.pucong.gitee.database.backup.business.sys.service.SysUserInfoService;
import com.pucong.gitee.database.backup.common.entity.database.AutoDataSource;
import com.pucong.gitee.database.backup.common.entity.database.dto.AutoDataSourceKeywordsDto;
import com.pucong.gitee.database.backup.common.entity.database.dto.DatabaseDto;
import com.pucong.gitee.database.backup.common.entity.database.dto.SelectDbBackTreeDto;
import com.pucong.gitee.database.backup.common.entity.database.vo.*;
import com.pucong.gitee.database.backup.common.entity.sys.SysFile;
import com.pucong.gitee.database.backup.common.entity.sys.UserJwt;
import com.pucong.gitee.database.backup.common.entity.sys.jwt.CheckResult;
import com.pucong.gitee.database.backup.common.entity.websock.DatabaseSqWebsocketVo;
import com.pucong.gitee.database.backup.common.selfenum.DatabaseExeStatusEnum;
import com.pucong.gitee.database.backup.common.selfenum.FileStatusEnum;
import com.pucong.gitee.database.backup.common.selfenum.TreeType;
import com.pucong.gitee.database.backup.configuration.exception.BusinessException;
import com.pucong.gitee.database.backup.utils.ApplicationContextUtil;
import com.pucong.gitee.database.backup.utils.JwtUtils;
import com.pucong.gitee.database.backup.utils.SqlUtils;
import com.pucong.gitee.database.backup.utils.ThreadPoolUtils;
import com.pucong.gitee.database.backup.utils.UUID;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVPrinter;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.websocket.Session;
import java.io.*;
import java.math.BigDecimal;
import java.sql.Timestamp;
import java.util.*;
import java.util.concurrent.ConcurrentHashMap;
import java.util.stream.Collectors;

@Service
@Slf4j
public class OperationDataSqlServiceImpl implements OperationDataSqlService {

    @Autowired
    private DatabaseStaticService databaseStaticService;

    @Autowired
    private AutoDataSourceService autoDataSourceService;

    @Autowired
    private SysUserInfoService sysUserInfoService;

    @Autowired
    private AutoDataSourceMapper autoDataSourceMapper;

    @Autowired
    private BackupServiceFactory backupServiceFactory;

    @Autowired
    private AutoExeSqlLogMapper autoExeSqlLogMapper;

    @Autowired
    private SysUserInfoMapper sysUserInfoMapper;

    @Autowired
    private SysFileMapper sysFileMapper;

    //存放连接信息的map
    private static Map<String, Session> sqlExecMap = new ConcurrentHashMap<>();

    @Override
    public void doMsg(String message, Session session) {
        ObjectMapper objectMapper = new ObjectMapper();
        DatabaseDto databaseDto = null;
        try {
            databaseDto = objectMapper.readValue(message, DatabaseDto.class);
        } catch (IOException e) {
            log.error("Json转换异常");
            log.error("异常信息:{}", e);
            throw new BusinessException("传入参数错误");
        }
        // 校验用户信息
        String token = databaseDto.getToken();
        UserJwt userJwt = checkUserPermission(token, databaseDto.getDataSourceId());
        // 处理消息
        DatabaseDto dto = databaseDto;
        // 启动线程异步处理
        ThreadPoolUtils.executor(() -> {
            if (dto.getExeStatusEnum() == DatabaseExeStatusEnum.执行) {
                try {
                    RunSqlResultPage runSqlResultPage = executeSql(userJwt.getUuid(), dto);
                    // 发送消息
                    DatabaseSqWebsocketVo vo = new DatabaseSqWebsocketVo();
                    vo.setExeStatusEnum(dto.getExeStatusEnum());
                    vo.setExeSqlResultPage(runSqlResultPage);
                    vo.setExecSuccess(true);

                    String resStr = objectMapper.writeValueAsString(vo);
                    session.getBasicRemote().sendText(resStr);
                } catch (Exception e) {
                    // 发送消息
                    try {
                        DatabaseSqWebsocketVo vo = new DatabaseSqWebsocketVo();
                        vo.setExeStatusEnum(dto.getExeStatusEnum());
                        vo.setExecSuccess(false);
                        vo.setErrorContent(e.getMessage());

                        String resStr = objectMapper.writeValueAsString(vo);
                        session.getBasicRemote().sendText(resStr);
                    } catch (IOException e2) {
                        log.error("发送消息失败:", e2);
                        throw new BusinessException("发送消息失败");
                    }
                }
            }else if (dto.getExeStatusEnum() == DatabaseExeStatusEnum.解释执行) {
                try {
                    OperationService operationService = databaseStaticService.getConnectionById(userJwt.getUuid(), dto.getDataSourceId(), dto.getDatabaseName(), dto.getSchemaName());
                    // 发送消息
                    ExeSqlResult exeSqlResult = operationService.executeQuery(" explain " + dto.getSql(), null);
                    DatabaseSqWebsocketVo vo = new DatabaseSqWebsocketVo();
                    vo.setExeStatusEnum(dto.getExeStatusEnum());
                    vo.setExeSqlResult(exeSqlResult);
                    vo.setExecSuccess(true);

                    String resStr = objectMapper.writeValueAsString(vo);
                    session.getBasicRemote().sendText(resStr);
                } catch (Exception e) {
                    // 发送消息
                    try {
                        DatabaseSqWebsocketVo vo = new DatabaseSqWebsocketVo();
                        vo.setExeStatusEnum(dto.getExeStatusEnum());
                        vo.setExecSuccess(false);
                        vo.setErrorContent(e.getMessage());

                        String resStr = objectMapper.writeValueAsString(vo);
                        session.getBasicRemote().sendText(resStr);
                    } catch (IOException e2) {
                        log.error("发送消息失败:", e2);
                        throw new BusinessException("发送消息失败");
                    }
                }
            }else if (dto.getExeStatusEnum() == DatabaseExeStatusEnum.导出数据到csv || dto.getExeStatusEnum() == DatabaseExeStatusEnum.导出数据到excel) {
                OperationService operationService = databaseStaticService.getConnectionById(userJwt.getUuid(), dto.getDataSourceId(), dto.getDatabaseName(), dto.getSchemaName());
                try {
                    String fileId = downDataBySql(userJwt.getUuid(), dto, operationService);
                    // 发送消息
                    DatabaseSqWebsocketVo vo = new DatabaseSqWebsocketVo();
                    vo.setExeStatusEnum(dto.getExeStatusEnum());
                    vo.setExecSuccess(true);
                    vo.setExportFileId(fileId);

                    String resStr = objectMapper.writeValueAsString(vo);
                    session.getBasicRemote().sendText(resStr);
                } catch (Exception e) {
                    log.error("发送消息失败:", e);
                    // 发送消息
                    try {
                        DatabaseSqWebsocketVo vo = new DatabaseSqWebsocketVo();
                        vo.setExeStatusEnum(dto.getExeStatusEnum());
                        vo.setExecSuccess(false);
                        vo.setErrorContent(e.getMessage());

                        String resStr = objectMapper.writeValueAsString(vo);
                        session.getBasicRemote().sendText(resStr);
                    } catch (IOException e2) {
                        log.error("发送消息失败:", e2);
                        throw new BusinessException("发送消息失败");
                    }
                } finally {
                    operationService.closeAll();
                }
            } else {
                try {
                    autoTransactionalCommit(session.getId(), dto);
                    // 发送消息
                    DatabaseSqWebsocketVo vo = new DatabaseSqWebsocketVo();
                    vo.setExeStatusEnum(dto.getExeStatusEnum());
                    vo.setExecSuccess(true);

                    String resStr = objectMapper.writeValueAsString(vo);
                    session.getBasicRemote().sendText(resStr);
                } catch (Exception e) {
                    log.error("发送消息失败:", e);
                    // 发送消息
                    try {
                        DatabaseSqWebsocketVo vo = new DatabaseSqWebsocketVo();
                        vo.setExeStatusEnum(dto.getExeStatusEnum());
                        vo.setExecSuccess(false);
                        vo.setErrorContent(e.getMessage());

                        String resStr = objectMapper.writeValueAsString(vo);
                        session.getBasicRemote().sendText(resStr);
                    } catch (IOException e2) {
                        log.error("发送消息失败:", e2);
                        throw new BusinessException("发送消息失败");
                    }
                }
            }
        });
    }

    /**
     * 下载sql查询结果
     *
     * @param userId
     * @param dto
     * @return
     * @throws Exception
     */
    private String downDataBySql(String userId, DatabaseDto dto, OperationService operationService) throws Exception {
        String sql = dto.getSql();

        if (!SqlUtils.isSelectSql(sql)) {
            throw new BusinessException("导出错误，非查询语句");
        }
        Integer pageSize = 10000;
        String tmpFilePath = ApplicationContextUtil.getTmpPath() + File.separator + UUID.uuid();
        File file = new File(tmpFilePath);
        if (!file.exists()) {
            file.createNewFile();
        }
        FileOutputStream fos = new FileOutputStream(tmpFilePath);
        if (dto.getExeStatusEnum() == DatabaseExeStatusEnum.导出数据到csv) {
            // 导出数据
            OutputStreamWriter osw = new OutputStreamWriter(fos, "GBK");
            CSVPrinter csvPrinter = null;
            for (Integer pageNum = 1; ; pageNum++) {
                // 分页查询
                RunSqlResultPage runSqlResultPage = operationService.executeQueryPage(pageNum, pageSize, sql, null);
                List<ResultFiledVo> filedList = runSqlResultPage.getFiledList();
                List<Map<String, Object>> valueList = runSqlResultPage.getExecResultMapPage().getRecords();
                if (pageNum == 1) {
                    // 设置表头
                    List<String> filedList2 = filedList.stream().map(ResultFiledVo::getFiledName).collect(Collectors.toList());
                    String[] filedStrs = filedList2.toArray(new String[filedList2.size()]);
                    CSVFormat csvFormat = CSVFormat.DEFAULT.withHeader(filedStrs);
                    csvPrinter = new CSVPrinter(osw, csvFormat);
                }
                if (valueList.size() == 0) {
                    break;
                }
                // 设置数据
                for (int j = 0; j < valueList.size(); j++) {
                    Map<String, Object> stringObjectMap = valueList.get(j);
                    Collection<Object> values = stringObjectMap.values();
                    Object[] valueStrs = values.toArray(new Object[values.size()]);
                    csvPrinter.printRecord(valueStrs);
                }
            }
            csvPrinter.flush();
            csvPrinter.close();
        } else if (dto.getExeStatusEnum() == DatabaseExeStatusEnum.导出数据到excel) {
            Workbook workbook = new XSSFWorkbook();
            Sheet sheet = workbook.createSheet(UUID.getSerializNo());
            int rowIndex = 0;
            for (Integer pageNum = 1; ; pageNum++) {
                // 分页查询
                RunSqlResultPage runSqlResultPage = operationService.executeQueryPage(pageNum, pageSize, sql, null);
                List<ResultFiledVo> filedList = runSqlResultPage.getFiledList();
                List<Map<String, Object>> valueList = runSqlResultPage.getExecResultMapPage().getRecords();
                int columnIndex = 0;
                if (pageNum == 1) {
                    // 设置表头
                    List<String> filedList2 = filedList.stream().map(ResultFiledVo::getFiledName).collect(Collectors.toList());
                    Row row = sheet.createRow(rowIndex++);
                    for (String key : filedList2) {
                        Cell cell = row.createCell(columnIndex++);
                        cell.setCellValue(key);
                    }
                }
                if (valueList.size() == 0) {
                    break;
                }
                // 设置数据
                if (valueList != null && !valueList.isEmpty()) {
                    for (Map<String, Object> map : valueList) {
                        Row row = sheet.createRow(rowIndex++);
                        columnIndex = 0;
                        Collection<Object> values = map.values();
                        Object[] valueStrs = values.toArray(new Object[values.size()]);
                        for (Object value : valueStrs) {
                            Cell cell = row.createCell(columnIndex++);
                            setCellValue(cell, value);
                        }
                    }
                }
            }
            workbook.write(fos);
        }
        fos.flush();
        fos.close();
        file = new File(tmpFilePath);
        // 新增文件记录
        SysFile sysFile = new SysFile();
        sysFile.setUuid(UUID.uuid());
        sysFile.setFileSize(file.length() + "");
        if (dto.getExeStatusEnum() == DatabaseExeStatusEnum.导出数据到csv) {
            sysFile.setFileNameOld(UUID.getSerializNo() + ".csv");
            sysFile.setFileType("csv");
        } else if (dto.getExeStatusEnum() == DatabaseExeStatusEnum.导出数据到excel) {
            sysFile.setFileNameOld(UUID.getSerializNo() + ".xlsx");
            sysFile.setFileType("xlsx");
        }

        sysFile.setCreateTime(new Date());
        sysFile.setCreateBy(userId);
        sysFile.setFileName("sql查询导出文件");
        sysFile.setFileStatus(FileStatusEnum.临时文件);
        sysFile.setPath(tmpFilePath);
        sysFileMapper.insert(sysFile);
        return sysFile.getUuid();
    }

    private static void setCellValue(Cell cell, Object obj) {
        if (obj == null) {
            return;
        }
        if (obj instanceof String) {
            cell.setCellValue((String) obj);
        } else if (obj instanceof Date) {
            Date date = (Date) obj;
            if (date != null) {
                cell.setCellValue(date);
            }
        } else if (obj instanceof Calendar) {
            Calendar calendar = (Calendar) obj;
            if (calendar != null) {
                cell.setCellValue(calendar);
            }
        } else if (obj instanceof Timestamp) {
            Timestamp timestamp = (Timestamp) obj;
            if (timestamp != null) {
                cell.setCellValue(timestamp);
            }
        } else if (obj instanceof Double) {
            cell.setCellValue((Double) obj);
        } else {
            cell.setCellValue(obj.toString());
        }
    }

    /**
     * 校验用户权限
     *
     * @param token
     * @param dbId
     * @return
     */
    @Override
    public UserJwt checkUserPermission(String token, String dbId) {
        if (StringUtils.isBlank(token)) {
            log.error("用户认证失败");
            throw new BusinessException("用户认证失败");
        }
        ObjectMapper objectMapper = new ObjectMapper();
        CheckResult checkResult = JwtUtils.validateJWT(token);
        if (checkResult.isSuccess()) {
            // 获取用户信息
            String subject = checkResult.getClaims().getSubject();
            UserJwt loginUser = null;
            try {
                loginUser = objectMapper.readValue(subject, UserJwt.class);
            } catch (JsonProcessingException e) {
                throw new BusinessException("反序列化用户数据失败");
            }
            // 校验用户权限
            List<String> userIds = null;
            if (!"superadmin".equals(loginUser.getLoginName())) {
                userIds = sysUserInfoMapper.selectChildrenUserId(loginUser.getUuid());
            }
            LambdaQueryWrapper<AutoDataSource> serviceComputerParams = new LambdaQueryWrapper<>();
            serviceComputerParams.eq(AutoDataSource::getUuid, dbId);
            serviceComputerParams.in(userIds != null, AutoDataSource::getCreateBy, userIds);
            AutoDataSource autoDataSource = autoDataSourceMapper.selectOne(serviceComputerParams);
            if (autoDataSource == null) {
                throw new BusinessException("该用户没有操作该数据源的权限");
            }
            return loginUser;
        } else {
            throw new BusinessException("用户未授权，无法访问");
        }
    }

    @Override
    public RunSqlResultPage executeSql(String sessionId, DatabaseDto dto) {
        OperationService operationService = databaseStaticService.getConnectionById(sessionId, dto.getDataSourceId(), dto.getDatabaseName(), dto.getSchemaName());
        String sql = dto.getSql();
        Object[] params = null;
        RunSqlResultPage exeSqlResult = new RunSqlResultPage();

        if (!SqlUtils.isSelectSql(sql)) {
            Date date1 = new Date();
            // 新增、修改、删除
            int i = operationService.executeUpdate(sql, params);
            exeSqlResult.setResultInteger(new BigDecimal(i));
            exeSqlResult.setSessionId(operationService.getSessionId());
            exeSqlResult.setExecSql(sql);
            exeSqlResult.setExecSqlParams(params);
            Date date2 = new Date();
            exeSqlResult.setExecTime(date2.getTime() - date1.getTime());
        } else {
            // 分页查询
            exeSqlResult = operationService.executeQueryPage(dto.getPageNum(), dto.getPageSize(), sql, params);
        }
        operationService.closeAll();
        return exeSqlResult;
    }

    @Override
    public void autoTransactionalCommit(String sessionId, DatabaseDto dto) {
        OperationService operationService = databaseStaticService.getConnectionById(sessionId, dto.getDataSourceId(), dto.getDatabaseName(), dto.getSchemaName());
        if (DatabaseExeStatusEnum.提交事物 == dto.getExeStatusEnum()) {
            operationService.commitTransactional();
        } else if (DatabaseExeStatusEnum.关闭事物 == dto.getExeStatusEnum()) {
            operationService.falseAutoCommit();
        } else if (DatabaseExeStatusEnum.开启事物 == dto.getExeStatusEnum()) {
            operationService.trueAutoCommit();
        } else if (DatabaseExeStatusEnum.停止执行 == dto.getExeStatusEnum()) {
            operationService.stopExec();
        }
        operationService.closeAll();
    }

    @Override
    public List<DbBackTreeVo> getDataSourceTreeList(SelectDbBackTreeDto dto) {
        List<DbBackTreeVo> dbBackTreeVos = new ArrayList<>();
        if (StringUtils.isNotBlank(dto.getUuid())) {
            return autoDataSourceService.getTreeList(dto);
        } else {
            /**
             * 查询一级列表
             * 权限查询
             */
            List<String> ids = sysUserInfoService.selectChildrenUserId();
            // 数据源
            LambdaQueryWrapper<AutoDataSource> backupDatabaseParams = new LambdaQueryWrapper<>();
            backupDatabaseParams.in(ids != null, AutoDataSource::getCreateBy, ids);
            List<AutoDataSource> autoDataSources = autoDataSourceMapper.selectList(backupDatabaseParams);

            for (AutoDataSource autoDataSource : autoDataSources) {
                DbBackTreeVo dbBackTreeVo = new DbBackTreeVo();
                dbBackTreeVo.setId(autoDataSource.getUuid());
                dbBackTreeVo.setDataSourceId(autoDataSource.getUuid());
                dbBackTreeVo.setPId("0");
                dbBackTreeVo.setTreeName(autoDataSource.getConnectName());
                dbBackTreeVo.setTreeType(TreeType.数据源);
                dbBackTreeVo.setDatabaseType(autoDataSource.getDatabaseType());
                dbBackTreeVos.add(dbBackTreeVo);
            }
            return dbBackTreeVos;
        }
    }

    @Override
    public List<DbTableFiledVo> getTableFiledListByDBInfo(AutoDataSourceKeywordsDto dto) {
        // 查询表和字段信息
        AutoDataSource db = autoDataSourceMapper.selectById(dto.getDataSourceId());
        db.setDatabaseName(dto.getDatabaseName());
        db.setSchemaName(dto.getSchemaName());
        // 查询数据源下的表
        OperationService backupService = backupServiceFactory.getBackupService(db, autoExeSqlLogMapper);
        List<DbTableFiledVo> tableFiledListByDBInfo = backupService.getTableFiledListByDBInfo(dto.getDatabaseName(), dto.getSchemaName());

        return tableFiledListByDBInfo;
    }

    @Override
    public void initConnection(Session session) {
        String uuid = session.getId();
        sqlExecMap.put(uuid, session);
    }

    @Override
    public void close(Session session) {
        String id = session.getId();
        //map中移除
        sqlExecMap.remove(id);
    }
}
